package com.st.lotte.util;


/*
 * INSERT 문
INSERT INTO TLO_DELY_TRCE_RECV_IF values (
  0, sysdate(), '0','0','12','0','11',
  sysdate(),'001','001','001','579847976',
  '7692017243', '02', '02-121-1212',
  '01112311123','서울','부산', '11',
   '20140914J19189',
   'test1','test2','test3','test4','test5',
   'test6','test7','test8','test9','test10',
   '1',
  'JDBC error reported: (SQLState = HY000) - java.sql.SQLException: Incorrect datetime value: 20140925 for function str_to_date',
  sysdate(),
  'EAI','20140223'
  )
// 실제 insert 가능한 쿼리문 샘플 
 * */

public class DBUtil {
	public static final String ONEDAY_BEFORE_COMPLETE = "SELECT COMPANY_CODE, RECV_DATE, CMBN_ORD_NO, SEQ, HODECO_CODE,RECV_BIZS_DIV_CODE,TRCE_STAT_CODE, ORD_NO, ORD_GOODS_SEQ, ORD_GOODS_DTL_SEQ, ORD_GOODS_DELY_SEQ, ORD_DTL_SN, SLIP_NO, SYS_BASE_CODE, LOCA_TELNO, STAFF_TELNO,HODECO_DTPL_CNTT,HODECO_ARRIPL_CNTT,TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT, RMK1, RMK2, RMK3, RMK4, RMK5, RMK6, RMK7, RMK8, RMK9, RMK10, INTF_STAT_CODE, INTF_ERR_CNTT, INTF_DT, CHGMN_ID, CHG_DT from TLO_DELY_TRCE_RECV_IF where RECV_DATE>=? and RECV_DATE<=? ";
	
	public static final String RECV_SELECTALL = "SELECT COMPANY_CODE, RECV_DATE, CMBN_ORD_NO, SEQ, HODECO_CODE,RECV_BIZS_DIV_CODE,TRCE_STAT_CODE, ORD_NO, ORD_GOODS_SEQ, ORD_GOODS_DTL_SEQ, ORD_GOODS_DELY_SEQ, ORD_DTL_SN, SLIP_NO, SYS_BASE_CODE, LOCA_TELNO, STAFF_TELNO,HODECO_DTPL_CNTT,HODECO_ARRIPL_CNTT,TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT, RMK1, RMK2, RMK3, RMK4, RMK5, RMK6, RMK7, RMK8, RMK9, RMK10, INTF_STAT_CODE, INTF_ERR_CNTT, INTF_DT, CHGMN_ID, CHG_DT from TLO_DELY_TRCE_RECV_IF where slip_no = ?";
	public static final String INSERT = "INSERT INTO TLO_DELY_TRCE_RECV_IF (COMPANY_CODE,RECV_DATE,CMBN_ORD_NO,SEQ,HODECO_CODE,RECV_BIZS_DIV_CODE,TRCE_STAT_CODE,ORD_NO,ORD_GOODS_SEQ,ORD_GOODS_DTL_SEQ,ORD_GOODS_DELY_SEQ,ORD_DTL_SN,SLIP_NO,SYS_BASE_CODE,LOCA_TELNO,STAFF_TELNO, HODECO_DTPL_CNTT,HODECO_ARRIPL_CNTT,TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT,RMK1,RMK2,RMK3,RMK4,RMK5,RMK6,RMK7,RMK8,RMK9,RMK10,INTF_STAT_CODE,INTF_ERR_CNTT,INTF_DT,CHGMN_ID,CHG_DT) "
			+" values (?, sysdate(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate(),?,sysdate())";
	// public static String POLL_SELECTALL = "select COMPANY_CODE,TRMN_DATE,CMBN_ORD_NO,SEQ,HODECO_CODE,TRMN_BIZS_DIV_CODE,ORD_NO,ORD_GOODS_SEQ,ORD_GOODS_DTL_SEQ,ORD_GOODS_DELY_SEQ,ORD_DTL_SN,SLIP_NO,SYS_BASE_CODE,RMK1,RMK2,RMK3,RMK4,RMK5,RMK6,RMK7,RMK8,RMK9,RMK10,INTF_STAT_CODE,INTF_ERR_CNTT,INTF_DT,CHGMN_ID,CHG_DT,CNT from TLO_DELY_TRCE_TRMN_IF where COMPLETE='N' AND INTF_STAT_CODE=1 AND TRMN_DATE>='2014-10-23'";
	public static final String POLL_SELECTALL = "select COMPANY_CODE,TRMN_DATE,CMBN_ORD_NO,SEQ,HODECO_CODE,TRMN_BIZS_DIV_CODE,ORD_NO,ORD_GOODS_SEQ,ORD_GOODS_DTL_SEQ,ORD_GOODS_DELY_SEQ,ORD_DTL_SN,SLIP_NO,SYS_BASE_CODE,RMK1,RMK2,RMK3,RMK4,RMK5,RMK6,RMK7,RMK8,RMK9,RMK10,INTF_STAT_CODE,INTF_ERR_CNTT,INTF_DT,CHGMN_ID,CHG_DT,CNT from TLO_DELY_TRCE_TRMN_IF where COMPLETE='N' AND INTF_STAT_CODE='1' AND TRMN_DATE>='2014-10-21'";
	public static final String VALIDATE_SELECTALL = "select COMPANY_CODE,TRMN_DATE,CMBN_ORD_NO,SEQ,HODECO_CODE,TRMN_BIZS_DIV_CODE,ORD_NO,ORD_GOODS_SEQ,ORD_GOODS_DTL_SEQ,ORD_GOODS_DELY_SEQ,ORD_DTL_SN,SLIP_NO,SYS_BASE_CODE,RMK1,RMK2,RMK3,RMK4,RMK5,RMK6,RMK7,RMK8,RMK9,RMK10,INTF_STAT_CODE,INTF_ERR_CNTT,INTF_DT,CHGMN_ID,CHG_DT,CNT from TLO_DELY_TRCE_TRMN_IF where COMPLETE='N' AND INTF_STAT_CODE='0' ";
	public static final String INSERT_TRCE ="INSERT INTO TLO_DELY_TRCE_TRMN_IF values (?,sysdate(),?,,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate(),?,sysdate())";
	public static final String UPDATE_TRACE = "UPDATE TLO_DELY_TRCE_TRMN_IF SET INTF_STAT_CODE=?, INTF_ERR_CNTT=?  WHERE SLIP_NO=? AND COMPLETE='N' AND INTF_STAT_CODE=0 ";
//	public static String UPDATE_COMPLETE = "UPDATE TLO_DELY_TRCE_TRMN_IF SET CNT=%d, COMPLETE='%s' WHERE SLIP_NO='%s'";
	
	/*
	 * 롯데 정산관련 쿼리문
	 * */
	public static final String TOTAL_COUNT="select RECV_DATE, count(*) as count from ( select RECV_DATE, SLIP_NO from TLO_DELY_TRCE_RECV_IF  where RECV_DATE>=? and RECV_DATE<=? GROUP BY SLIP_NO ) as T group by RECV_DATE ";
	public static final String COMPLETE_COUNT = "select T.RECV_DATE as RECV_DATE, count(*) as count  from ( select RECV_DATE, MAX(SEQ), SLIP_NO from TLO_DELY_TRCE_RECV_IF  where RECV_DATE>=? and RECV_DATE<=? and TRCE_STAT_CODE='16' GROUP BY SLIP_NO, TRCE_STAT_CODE ) as T group by T.RECV_DATE";
	public static final String NONCOMPLETE_COUNT = "select TRMN_DATE, count(*) as count from( select * from TLO_DELY_TRCE_TRMN_IF where COMPLETE='N' and INTF_STAT_CODE='1' and CNT=0 and TRMN_DATE>=? and TRMN_DATE<=? GROUP BY slip_no) as T GROUP BY TRMN_DATE ";
	public static final String INVALIDATE_COUNT = "select T.TRMN_DATE as TRMN_DATE, count(*) as count from (select TRMN_DATE, SLIP_NO from TLO_DELY_TRCE_TRMN_IF where TRMN_DATE>=? and TRMN_DATE<=? and INTF_STAT_CODE='9' GROUP BY SLIP_NO ) as T GROUP BY TRMN_DATE ";
	public static final String DELAY_ONETWO_COUNT = "select RECV_DATE, count(*) as count from( select  RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF  where RECV_DATE>=? and RECV_DATE<=? and RMK4!='인수자등록' and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>='24:00:00' and timediff<='48:00:00' group by T.RECV_DATE ";
	public static final String DELAY_TWOTHREE_COUNT = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE>=? and RECV_DATE<=? and RMK4!='인수자등록' and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>='48:00:00' and timediff<'72:00:00' group by T.RECV_DATE ";
	public static final String DELAY_THREEOVER_COUNT = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE>=? and RECV_DATE<=? and RMK4!='인수자등록' and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>='72:00:00' group by T.RECV_DATE ";
	
	
	//[ ## 관리자 메뉴에서 사용하는 쿼리 ## ]
	//택배사 코드 이름 매칭 
	public static final String GET_CODELIST ="SELECT code, name FROM code";
	public static final String GET_DELY_STAT_LIST ="SELECT code, name FROM DELY_STAT";
	
	// 전일 배송완료 
	public static final String BEFORE_COMPLETE ="select RECV_DATE, MAX(SEQ) as SEQ, SLIP_NO, CMBN_ORD_NO, HODECO_CODE, TRCE_STAT_CODE, RMK4, TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT from TLO_DELY_TRCE_RECV_IF  where RECV_DATE=(? - INTERVAL 1 DAY)  and TRCE_STAT_CODE='16' GROUP BY SLIP_NO, TRCE_STAT_CODE ";
	// 전일 배송출발 
	public static final String BEFORE_START ="select RECV_DATE, MAX(SEQ) as SEQ, SLIP_NO, CMBN_ORD_NO, HODECO_CODE, TRCE_STAT_CODE, RMK4, TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT from TLO_DELY_TRCE_RECV_IF  where RECV_DATE=(? - INTERVAL 1 DAY)  and TRCE_STAT_CODE='15' GROUP BY SLIP_NO, TRCE_STAT_CODE";
	// 당일 배송완료
	public static final String DAY_COMPLETE ="select RECV_DATE, MAX(SEQ) as SEQ, SLIP_NO, CMBN_ORD_NO, HODECO_CODE, TRCE_STAT_CODE, RMK4, TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT from TLO_DELY_TRCE_RECV_IF  where RECV_DATE=?  and TRCE_STAT_CODE='16' GROUP BY SLIP_NO, TRCE_STAT_CODE ";
	// 당일 배송출발 	
	public static final String DAY_START ="select RECV_DATE, MAX(SEQ) as SEQ, SLIP_NO, CMBN_ORD_NO, HODECO_CODE, TRCE_STAT_CODE, RMK4, TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT from TLO_DELY_TRCE_RECV_IF  where RECV_DATE=?  and TRCE_STAT_CODE='15' GROUP BY SLIP_NO, TRCE_STAT_CODE ";
	
	// 전체송장대상
	public static final String RECV_SELALL = "SELECT COMPANY_CODE, RECV_DATE, CMBN_ORD_NO, SEQ, HODECO_CODE,RECV_BIZS_DIV_CODE,TRCE_STAT_CODE, ORD_NO, ORD_GOODS_SEQ, ORD_GOODS_DTL_SEQ, ORD_GOODS_DELY_SEQ, ORD_DTL_SN, SLIP_NO, SYS_BASE_CODE, LOCA_TELNO, STAFF_TELNO,HODECO_DTPL_CNTT,HODECO_ARRIPL_CNTT,TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT, RMK1, RMK2, RMK3, RMK4, RMK5, RMK6, RMK7, RMK8, RMK9, RMK10, INTF_STAT_CODE, INTF_ERR_CNTT, INTF_DT, CHGMN_ID, CHG_DT from TLO_DELY_TRCE_RECV_IF where 1=1 ";
    // 전체송장대상 카운트 
	public static final String RECV_CNT = "SELECT count(*) as count from TLO_DELY_TRCE_RECV_IF where 1=1 ";
	// 미배송송장 건수 
	public static final String NON_DELIVERY_QUERY = "SELECT COMPANY_CODE, RECV_DATE, CMBN_ORD_NO, SEQ, HODECO_CODE,RECV_BIZS_DIV_CODE,TRCE_STAT_CODE, ORD_NO, ORD_GOODS_SEQ, ORD_GOODS_DTL_SEQ, ORD_GOODS_DELY_SEQ, ORD_DTL_SN, SLIP_NO, SYS_BASE_CODE, LOCA_TELNO, STAFF_TELNO,HODECO_DTPL_CNTT,HODECO_ARRIPL_CNTT,TRCE_STAT_DIV_CODE, TRCE_STAT_PROC_DT, RMK1, RMK2, RMK3, RMK4, RMK5, RMK6, RMK7, RMK8, RMK9, RMK10, INTF_STAT_CODE, INTF_ERR_CNTT, INTF_DT, CHGMN_ID, CHG_DT from TLO_DELY_TRCE_RECV_IF where 1=1 and TRCE_STAT_CODE<16 ";
	// 미배송송장 카운트 
	public static final String NON_DELIVERY_CNT = "SELECT count(*) as count from TLO_DELY_TRCE_RECV_IF where TRCE_STAT_CODE<16 ";

	

	
	// 미생성 송장건수
//	public static String NONCOMPLETE = "select COMPANY_CODE,TRMN_DATE,CMBN_ORD_NO,SEQ,HODECO_CODE,TRMN_BIZS_DIV_CODE,ORD_NO,ORD_GOODS_SEQ,ORD_GOODS_DTL_SEQ,ORD_GOODS_DELY_SEQ,ORD_DTL_SN,SLIP_NO,SYS_BASE_CODE,RMK1,RMK2,RMK3,RMK4,RMK5,RMK6,RMK7,RMK8,RMK9,RMK10,INTF_STAT_CODE,INTF_ERR_CNTT,INTF_DT,CHGMN_ID,CHG_DT,CNT from TLO_DELY_TRCE_TRMN_IF from TLO_DELY_TRCE_TRMN_IF where COMPLETE='N' and INTF_STAT_CODE='1' and CNT=0 and TRMN_DATE>='2014-11-01' and TRMN_DATE<='2014-11-25'GROUP BY slip_no";
	// 당일날 수신된 전체송장건수
//	public static String DELAY_ALL = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE=? and RMK4!='인수자등록'and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>='24:00:00' and timediff<='48:00:00' group by T.RECV_DATE ";
	//24시간이상 48시간 이하
	public static final String DELAY_ONETWO = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE=? and RMK4!='인수자등록'and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>='24:00:00' and timediff<='48:00:00' group by T.RECV_DATE ";
	//48시간초과 72시간 이하
	public static final String DELAY_TWOTHREE = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE=? and RMK4!='인수자등록'and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>'48:00:00' and timediff<='72:00:00' group by T.RECV_DATE ";
	//72시간초과
	public static final String DELAY_THREEOVER = "select RECV_DATE, count(*) as count from( select RECV_DATE, MAX(SEQ), TIMEDIFF(INTF_DT,TRCE_STAT_PROC_DT) as timediff from TLO_DELY_TRCE_RECV_IF where RECV_DATE=? and RMK4!='인수자등록'and TRCE_STAT_CODE='16' group by slip_no) as T where timediff>'72:00:00' group by T.RECV_DATE ";

	

	

	

}
